--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';


--CREATE TABLE se_cli_client AS (SELECT * FROM &erp.cli_client WHERE 1=2);


/*
SELECT &outils.stragg(source) source,client_cod, lieutrp_cod, prfweb_cod, condtrp_cod, modetrp_cod, mqimpl_cod, tierscateg_cod, classif_cod, zone_cod, enreg_id, origprosp_cod, devise_cod, niveau_cod, rgppiece_cod, sectstat_cod, enreg_s, aff_nom, clidivers_ok, abc_ok, rib_no, nombanque, vilbanque, iban_no, bic_no, factoring_cod, ctrlmqart_ok, effectif_nb, casoc_mt, casoc_aaaa, webacces, webmdp, autrecodif_cod, libre1_val, libre2_val, libre3_val, libre4_val, libre5_val, libre6_val, libre7_val, libre8_val, libre9_val, libre10_val
FROM (
  SELECT 'uv' source ,g.* FROM se_cli_client g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.cli_client g
  )
--where client_cod ='09625'
GROUP BY client_cod, lieutrp_cod, prfweb_cod, condtrp_cod, modetrp_cod, mqimpl_cod, tierscateg_cod, classif_cod, zone_cod, enreg_id, origprosp_cod, devise_cod, niveau_cod, rgppiece_cod, sectstat_cod, enreg_s, aff_nom, clidivers_ok, abc_ok, rib_no, nombanque, vilbanque, iban_no, bic_no, factoring_cod, ctrlmqart_ok, effectif_nb, casoc_mt, casoc_aaaa, webacces, webmdp, autrecodif_cod, libre1_val, libre2_val, libre3_val, libre4_val, libre5_val, libre6_val, libre7_val, libre8_val, libre9_val, libre10_val
HAVING Count(1)!=2
ORDER BY client_cod;*/

--select * from uv_cclients

DELETE FROM  se_cli_client ;


INSERT INTO se_cli_client
 (SELECT DISTINCT c.id client_cod,
         LIEUTRPCEE lieutrp_cod,
         NULL prfweb_cod,
         CONDTRPCEE condtrp_cod,
         MODETRPCEE modetrp_cod,
         NULL mqimpl_cod,
         Nvl(CATEGORIE,'00') tierscateg_cod,
         '000' classif_cod,
         NULL zone_cod,
         0 enreg_id,
         NULL origprosp_cod,
         Nvl(DEVISE,'EUR') devise_cod,
         CASE WHEN c.id<=999 THEN 'G' ELSE 'C' end niveau_cod,
         Decode(cdblfact||blfact,'NO','03','NN','01','OO','02','ON','02','01') rgppiece_cod,
         cm.SECSTA sectstat_cod,
         Decode(etat,'C','N','S','L','N') enreg_s,
         trim(SubStr(NOM || Nvl2(cp,' ' || cp,'') || Nvl2(bd,' ' || bd,'') || Nvl2(adresse1,' ' || adresse1,'') || Nvl2(compl_adresse,' ' || compl_adresse,''),1,80)) aff_nom,
         'N' clidivers_ok,
         Nvl(gestabc,'N') abc_ok,
         RIB rib_no,
         NOMBANQ nombanque,
         VILBANQ vilbanque,
         NULL iban_no,
         NULL bic_no,
         NULL factoring_cod,
         'O' ctrlmqart_ok,
         NULL effectif_nb,
         NULL casoc_mt,
         NULL casoc_aaaa,
         ACCESWEB webacces,
         MTPASSWEB webmdp,
         NULL autrecodif_cod,
         null libre1_val,
         null libre2_val,
         null libre3_val,
         NULL libre4_val,
         NULL libre5_val,
         NULL libre6_val,
         NULL libre7_val,
         NULL libre8_val,
         NULL libre9_val,
         NULL libre10_val
    FROM uv_cclients c,uv_cmarque_client cm
    WHERE cm.client(+)=c.id
      AND cm.MARQUE(+)='1'
    ) ;


 UPDATE se_cli_client tf
 SET enreg_id = (select enreg_id FROM &erp.cli_client f WHERE f.client_cod=tf.client_cod )
 WHERE EXISTS (SELECT 1 FROM &erp.cli_client f WHERE f.client_cod=tf.client_cod);

 UPDATE se_cli_client SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;


COMMIT;

